﻿namespace Busconeitor.DataLayer
{
    using Domain.Entities;
    using Domain.Utils.HashTable;
    using Domain.Utils.SimpleList;
    using log4net;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class TermDb
    {
        #region Atributos
        
        //STORE PROCEDURE para insertar un termino
        private static String INSERT_TERM = "TERM_Insert";
        //STORE PROCEDURE para insertar un termino sucio
        private static String INSERT_DIRTY_TERM = "DIRTYTERM_Insert";
        //STORE PROCEDURE para insertar un indice
        private static String INSERT_INDEX = "INDEX_Insert";
        //STORE PROCEDURE para insertar un posteo
        private static String INSERT_POST = "POST_Insert";
        //sql para verificar si existe un termino en la bd
        private static String EXIST_TERM = "SELECT idTerm, cleanTerm, nr, maxTf FROM TERM WHERE cleanTerm = ?";
        //sql para verificar si existe un termino sucio en la bd
        private static String EXIST_DIRTY_TERM = "SELECT idDirtyTerm, idTerm, term FROM DIRTYTERM WHERE term = ?";
        //sql para obtener todos los terminos q se encuentran en la lista de busqueda
        private static String GET_TERMS_QUERY = "SELECT * FROM TERM WHERE cleanTerm IN (?)"; 

        #endregion

        #region Constructores

        #endregion

        #region Propiedades

        #endregion

        #region Metodos

        /// <summary>
        /// Metodo para obtener un hashlist de terminos con todos los terminos de busqueda.
        /// </summary>
        /// <param name="pQueryTerms"></param>
        /// <returns></returns>
        public static HashList FindTerms(SimpleList pQueryTerms)
        {
            try
            {
                var listTerm = new HashList(20);
                // TODO: revisar esta linea
                var length = pQueryTerms.Size();
                //agrego a la query la cantidad de parametros como terminos tengo
                var queryItems = new StringBuilder();
                pQueryTerms.StartIterator();

                while (pQueryTerms.HasNext())
                {
                    var stringTerm = (string)pQueryTerms.Next();
                    queryItems.Append("'" + stringTerm + "',");
                }

                var query = GET_TERMS_QUERY.Replace("?", queryItems.ToString().Remove(queryItems.Length - 1, 1));
                var connection = DbConnection.GetDbInstance().GetDbConnection();
                var myCommand = new SqlCommand(query, connection);
                var result = myCommand.ExecuteReader();

                while (result.Read())
                {
                    var term = new TerminoEntity(result.GetInt32(0), result.GetInt32(2), result.GetInt32(3));
                    listTerm.Put(term);
                }

                result.Close();

                return listTerm;
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
                return null;
            }
        }

        /// <summary>
        /// Metodo para comprobar si existe el termino pasado por parametro en la base de datos.
        /// </summary>
        /// <param name="term"></param>
        /// <returns></returns>
        public static bool ExistTerm(TerminoEntity term)
        {
            try
            {
                //Preparo la consulta SQL con su parametro
                var query = EXIST_TERM.Replace("?", "'" + term.CleanTerm + "'");
                var connection = DbConnection.GetDbInstance().GetDbConnection();
                var myCommand = new SqlCommand(query, connection);
                var result = myCommand.ExecuteReader();

                if (result.Read())
                {
                    term.Id = (result.GetInt32(0));
                    return true;
                }

                result.Close();

                return false;
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
                return false;
            }
        }

        /// <summary>
        /// Metodo para comprobar si existe el termino sucio pasado por parametro en la base de datos.
        /// </summary>
        /// <param name="term"></param>
        /// <returns></returns>
        public static bool ExistDirtyTerm(TerminoSucioEntity term)
        {
            try
            {
                //Preparo la consulta SQL con su parametro
                var query = EXIST_DIRTY_TERM.Replace("?", "'" + term.PalabraVariada + "'");
                var connection = DbConnection.GetDbInstance().GetDbConnection();
                var myCommand = new SqlCommand(query, connection);
                var result = myCommand.ExecuteReader();

                if (result.Read())
                {
                    term.IdTerminoVariado = result.GetInt32(0);
                    return true;
                }

                result.Close();

                return false;
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());

                return false;
            }
        }

        /// <summary>
        /// Persiste un posteo en la base de datos.
        /// </summary>
        /// <param name="idDocument"></param>
        /// <param name="idTerm"></param>
        /// <param name="tf"></param>
        public static void SavePost(int idDocument, int idTerm, int tf)
        {
            try
            {
                //Preparo la consulta SQL con su parametro
                var connection = DbConnection.GetDbInstance().GetDbConnection();
                var myCommand = new SqlCommand();
                myCommand.Connection = connection;
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.CommandText = INSERT_POST;

                myCommand.Parameters.Add("@idDocument", SqlDbType.Int).Value = idDocument;
                myCommand.Parameters.Add("@idTerm", SqlDbType.Int).Value = idTerm;
                myCommand.Parameters.Add("@tf", SqlDbType.Int).Value = tf;

                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
            }
        }

        /// <summary>
        /// Persiste los indice de un termino sucio en la base de datos.
        /// </summary>
        /// <param name="idDocument"></param>
        /// <param name="idDirtyTerm"></param>
        /// <param name="index"></param>
        private static void SaveIndex(int idDocument, int idDirtyTerm, SimpleList index)
        {
            index.StartIterator();

            try
            {
                // Preparo la consulta SQL con su parametro
                var connection = DbConnection.GetDbInstance().GetDbConnection();

                while (index.HasNext())
                {
                    var myCommand = new SqlCommand();
                    myCommand.Connection = connection;
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = INSERT_INDEX;

                    var i = ((int)index.Next());

                    myCommand.Parameters.Add("@idDocument", SqlDbType.Int).Value = idDocument;
                    myCommand.Parameters.Add("@idDirtyTerm", SqlDbType.Int).Value = idDirtyTerm;
                    myCommand.Parameters.Add("@index", SqlDbType.Int).Value = i;

                    myCommand.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());
            }
        }

        /// <summary>
        /// Este metodo persiste en base de datos un termino, su lista de terminos sucios y sus respectivos indices.
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="termino"></param>
        /// <returns></returns>
        public static int SaveTerm(DocumentoEntity doc, TerminoEntity termino)
        {
            var connection = DbConnection.GetDbInstance().GetDbConnection();

            try
            {
                var myCommand = new SqlCommand();
                TerminoSucioEntity dirtyTerm;

                if (!ExistTerm(termino))
                {
                    myCommand.Connection = connection;
                    myCommand.CommandType = CommandType.StoredProcedure;
                    myCommand.CommandText = INSERT_TERM;

                    var paramId = new SqlParameter("@idTerm", SqlDbType.Int);
                    paramId.Direction = ParameterDirection.Output;
                    myCommand.Parameters.Add(paramId);

                    myCommand.Parameters.Add("@cleanTerm", SqlDbType.NVarChar).Value = termino.CleanTerm;
                    myCommand.Parameters.Add("@nr", SqlDbType.NVarChar).Value = termino.Nr;
                    myCommand.Parameters.Add("@maxTF", SqlDbType.Int).Value = termino.TfMax;

                    myCommand.ExecuteNonQuery();

                    termino.Id = (int)myCommand.Parameters["@idTerm"].Value;
                    // obtiene en un simple list las palabras sucias asociadas a esta palabra limpia.
                    var listPs = termino.Ps;
                    listPs.StartIterator();

                    while (listPs.HasNext())
                    {
                        dirtyTerm = (TerminoSucioEntity)listPs.Next();

                        if (!ExistDirtyTerm(dirtyTerm))
                        {
                            InsertDirtyTerm(termino, dirtyTerm, connection, myCommand);
                        }

                        SaveIndex(doc.Id, dirtyTerm.IdTerminoVariado, dirtyTerm.Indices);
                    }
                }
                else
                {
                    // Obtiene en un simple list las palabras sucias asociadas a esta palabra limpia.
                    var listPs = termino.Ps;
                    listPs.StartIterator();

                    while (listPs.HasNext())
                    {
                        dirtyTerm = (TerminoSucioEntity)listPs.Next();

                        if (!ExistDirtyTerm(dirtyTerm))
                        {
                            InsertDirtyTerm(termino, dirtyTerm, connection, myCommand);
                        }

                        SaveIndex(doc.Id, dirtyTerm.IdTerminoVariado, dirtyTerm.Indices);
                    }
                }

                SavePost(doc.Id, termino.Id, termino.TfDoc);

                return termino.Id;
            }
            catch (Exception ex)
            {
                var logger = LogManager.GetLogger("DataLayer");
                logger.Error(ex.ToString());

                return 0;
            }
        }

        private static void InsertDirtyTerm(TerminoEntity termino, TerminoSucioEntity dirtyTerm, SqlConnection connection, SqlCommand myCommand)
        {
            myCommand = new SqlCommand();
            myCommand.Connection = connection;
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.CommandText = INSERT_DIRTY_TERM;

            var pId = new SqlParameter("@idDirtyTerm", SqlDbType.Int);
            pId.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(pId);

            myCommand.Parameters.Add("@idTerm", SqlDbType.Int).Value = termino.Id;
            myCommand.Parameters.Add("@term", SqlDbType.NVarChar).Value = dirtyTerm.PalabraVariada;

            myCommand.ExecuteNonQuery();

            dirtyTerm.IdTerminoVariado = (int)myCommand.Parameters["@idDirtyTerm"].Value;
        }
 
        #endregion
    }
}
